import mysql.connector
from my_json import save_json


def count_grade():
    j = {}
    con = mysql.connector.connect(
        host="192.168.80.190",
        port=3306,
        user="hive",
        password="12345678",
        database="gaokao"
    )
    cursor = con.cursor()
    q = "select province_name, year,avg(min_grade) as a,count(*) as n from grade_plan where is_grade=1 and year>'2017' and batch_name like '%本科%' and type_name='普通类' GROUP BY province_name, year, school_name"
    q = "select province_name, year,avg(a) from ({}) as t1 where n>=4 GROUP BY province_name, year".format(q)
    cursor.execute(q)
    results = cursor.fetchall()
    q = {}
    for r_item in results:
        province_name = r_item[0]
        year = r_item[1]
        a = int(r_item[2])
        if province_name not in j:
            j[province_name] = {}
        j[province_name][year] = a
        if year not in q:
            q[year] = []
        q[year].append(a)
    j["全国"] = {}
    for year, grade in q.items():
        j["全国"][year] = int(sum(grade) / len(grade))
    save_json("../sql/avg_grade.json", j)


count_grade()
